﻿Imports System.Data.SqlClient
Imports System.Configuration

Public Class Maestro

    Private mConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")


    Function Agregar(ByVal mantenimiento As Ejemplo.BE.Maestro) As Ejemplo.BE.Maestro
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()
        
        Dim parametroId As SqlParameter


        Try
            sqlConnection.ConnectionString = mConnectionString

            parametroId = New SqlParameter("Id", mantenimiento.Id)
            parametroId.Direction = ParameterDirection.Output

            sqlCommand.Parameters.Add(parametroId)
            sqlCommand.Parameters.Add(New SqlParameter("Nombre", mantenimiento.Nombre))
            sqlCommand.Parameters.Add(New SqlParameter("Descripcion", mantenimiento.Descripcion))

            sqlConnection.Open()

            sqlCommand.CommandText = "MaestroAgregar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()
            mantenimiento.Id = CInt(parametroId.Value)

        Catch Ex As Exception
            mantenimiento = Nothing
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try
        Return mantenimiento

    End Function

    Function Editar(ByVal mantenimiento As Ejemplo.BE.Maestro) As Boolean
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()

        Try
            sqlConnection.ConnectionString = mConnectionString


            sqlCommand.Parameters.Add(New SqlParameter("Nombre", mantenimiento.Nombre))
            sqlCommand.Parameters.Add(New SqlParameter("Descripcion", mantenimiento.Descripcion))
            sqlCommand.Parameters.Add(New SqlParameter("Id", mantenimiento.Id))

            sqlConnection.Open()

            sqlCommand.CommandText = "MaestroEditar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Editar en la Bd" & Ex.ToString)
            Return False
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try

        Return True
    End Function

    Function Eliminar(ByVal id As Integer) As Boolean
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()

        Try
            sqlConnection.ConnectionString = mConnectionString
            sqlCommand.Parameters.Add(New SqlParameter("Id", id))

            sqlConnection.Open()

            sqlCommand.CommandText = "MaestroEliminar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Eliminar en la Bd" & Ex.ToString)
            Return False
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try

        Return True
    End Function

    Function Listar() As List(Of Ejemplo.BE.Maestro)

        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()
        Dim myReader As SqlDataReader
        Dim lista As New List(Of Ejemplo.BE.Maestro)

        Try
            sqlConnection.ConnectionString = mConnectionString

            sqlConnection.Open()

            sqlCommand.CommandText = "MaestroListar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection

            myReader = sqlCommand.ExecuteReader()

            If myReader.HasRows Then
                Do While myReader.Read()

                    Dim mantenimiento As New Ejemplo.BE.Maestro
                    mantenimiento.Id = CInt(myReader.GetValue(0))
                    mantenimiento.Nombre = myReader.GetValue(1).ToString
                    mantenimiento.Descripcion = myReader.GetValue(2).ToString
                    lista.Add(mantenimiento)

                Loop

            End If

            myReader.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al listar" & Ex.ToString)
        Finally

            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try


        Return lista
    End Function

    Function Obtener(ByVal id As Integer) As Ejemplo.BE.Maestro
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()
        Dim myReader As SqlDataReader
        Dim resultado As Ejemplo.BE.Maestro = Nothing

        Try
            sqlConnection.ConnectionString = mConnectionString

            sqlConnection.Open()

            sqlCommand.CommandText = "MaestroObtener"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection

            sqlCommand.Parameters.Add(New SqlParameter("Id", id))

            myReader = sqlCommand.ExecuteReader()

            If myReader.HasRows Then
                Do While myReader.Read()
                    resultado = New Ejemplo.BE.Maestro
                    resultado.Id = CInt(myReader.GetValue(0))
                    resultado.Nombre = myReader.GetValue(1).ToString
                    resultado.Descripcion = myReader.GetValue(2).ToString
                Loop

            End If

            myReader.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Obtener" & Ex.ToString)
        Finally

            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try


        Return resultado
    End Function


End Class
